package com.ms.server.init;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;

import com.suyunyou.manager.utils.SysUserUtil;
import com.system.comm.utils.FrameSpringBeanUtil;
import com.system.comm.utils.FrameStringUtil;
import com.system.dao.BaseDao;
import com.system.ds.DbContextHolder;

/**
 * 脚本升级
 * 		--测试sql 爬java资料 
		insert  into `spider_site`(`site_id`,`type`,`name`,`url`,`domain`,`is_enable`) values (1,10,'java入门资料','http://www.runoob.com/java/','http://www.runoob.com/java/',1);
		insert  into `spider_rule`(`rule_id`,`regex`,`title_select`,`content_select`,`site_id`,`is_enable`) values (1,'http://www.runoob.com/java/[\w|\d|-]+.html','#content>h1','#content',1,1);

		--测试sql 爬微信公众号的最新内容
		insert  into `spider_site`(`site_id`,`type`,`name`,`url`,`domain`,`is_enable`) values (2,20,'巨国贤','https://mp.weixin.qq.com/s?','http://weixin.sogou.com/weixin?type=1&s_from=input&query=%E5%B7%A8%E5%9B%BD%E8%B4%A4',1);
		insert  into `spider_rule`(`rule_id`,`regex`,`title_select`,`content_select`,`site_id`,`is_enable`) values (2,'https://mp.weixin.qq.com/profile?src=3&timestamp=1516936405&ver=1&signature=uPB8oKFEO2MOmtfGg3iI9tPNQr4rvXQravqPzRYhKmWoCu8ytfXavoF5Ty-s74CO5TmvFh8jf*w6jhIkLuBtXQ==','#page-content>#img-content>h2','#page-content>#img-content>.rich_media_content',2,1);
		
 * @author yuejing
 * @date 2018年12月18日 上午11:17:25
 */
public class DbUpdate {

	private static final Logger LOGGER = LoggerFactory.getLogger(DbUpdate.class);

	/**
	 * 初始化更新后的脚本
	 */
	public static void init() {
		/*if (EnvUtil.projectDbUpdate() != 1) {
			return;
		}*/
		ThreadPoolTaskExecutor task = FrameSpringBeanUtil.getBean(ThreadPoolTaskExecutor.class);
		task.execute(new Runnable() {

			@Override
			public void run() {
				updateSql();
			}

		});
	}
	private static void updateSql() {
		DbContextHolder.setDsName("dataSource1");
		BaseDao baseDao = new BaseDao();
		version1_0_0(baseDao);
		//version1_0_1(baseDao);
	}
	private static void version1_0_0(BaseDao baseDao) {
		createSysUser(baseDao);
		createSysConfig(baseDao);

		createSpiderSite(baseDao);
		createSpiderRule(baseDao);
		createSpiderLink(baseDao);

		createPageInfo(baseDao);

		// 初始化爬取内容的测试脚本
		/*--测试sql 爬java资料 
		insert  into `spider_site`(`site_id`,`type`,`name`,`url`,`domain`,`is_enable`) values (1,10,'java入门资料','http://www.runoob.com/java/','http://www.runoob.com/java/',1);
		insert  into `spider_rule`(`rule_id`,`regex`,`title_select`,`content_select`,`site_id`,`is_enable`) values (1,'http://www.runoob.com/java/[\w|\d|-]+.html','#content>h1','#content',1,1);

		--测试sql 爬微信公众号的最新内容
		insert  into `spider_site`(`site_id`,`type`,`name`,`url`,`domain`,`is_enable`) values (2,20,'巨国贤','https://mp.weixin.qq.com/s?','http://weixin.sogou.com/weixin?type=1&s_from=input&query=%E5%B7%A8%E5%9B%BD%E8%B4%A4',1);
		insert  into `spider_rule`(`rule_id`,`regex`,`title_select`,`content_select`,`site_id`,`is_enable`) values (2,'https://mp.weixin.qq.com/profile?src=3&timestamp=1516936405&ver=1&signature=uPB8oKFEO2MOmtfGg3iI9tPNQr4rvXQravqPzRYhKmWoCu8ytfXavoF5Ty-s74CO5TmvFh8jf*w6jhIkLuBtXQ==','#page-content>#img-content>h2','#page-content>#img-content>.rich_media_content',2,1);
		*/
	}
	private static void createPageInfo(BaseDao baseDao) {
		try {
			baseDao.queryForLong("select count(*) from page_info where 1=2");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[page_info]: " + e.getMessage());
			String sql = "CREATE TABLE `page_info` (\r\n" + 
					"  `page_id` int(11) NOT NULL auto_increment COMMENT '编号',\r\n" + 
					"  `title` varchar(200) NOT NULL COMMENT '标题',\r\n" + 
					"  `content` longtext NOT NULL COMMENT '正文',\r\n" + 
					"  `html` longtext NOT NULL COMMENT '完整html',\r\n" + 
					"  `spider_time` datetime NOT NULL COMMENT '爬取时间',\r\n" + 
					"  `author` varchar(30) default NULL COMMENT '作者',\r\n" + 
					"  `spider_url` varchar(255) NOT NULL COMMENT '爬取地址',\r\n" + 
					"  `site_id` int(11) NOT NULL COMMENT '来源域名编号',\r\n" + 
					"  PRIMARY KEY  (`page_id`),\r\n" + 
					"  UNIQUE KEY `uq_spider_url` (`spider_url`),\r\n" + 
					"  KEY `idx_site_id_spider_time` (`site_id`,`spider_time`)\r\n" + 
					") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='爬取的页面信息表';";
			batchExec(sql, baseDao);
			LOGGER.info("|===================================================");
		}
	}
	private static void createSpiderLink(BaseDao baseDao) {
		try {
			baseDao.queryForLong("select count(*) from spider_link where 1=2");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[spider_link]: " + e.getMessage());
			String sql = "CREATE TABLE `spider_link` (\r\n" + 
					"  `link_id` int(11) NOT NULL auto_increment COMMENT '编号',\r\n" + 
					"  `link` varchar(255) NOT NULL COMMENT '链接',\r\n" + 
					"  `md5_link` varchar(150) NOT NULL COMMENT 'md5(link)',\r\n" + 
					"  `domain` varchar(255) NOT NULL COMMENT '来源域名',\r\n" + 
					"  `content` longtext COMMENT '内容',\r\n" + 
					"  `create_time` datetime NOT NULL COMMENT '创建时间',\r\n" + 
					"  `is_fetcher_content` int(11) NOT NULL COMMENT '是否提取内容',\r\n" + 
					"  `fetcher_content_time` datetime default NULL COMMENT '提取内容时间',\r\n" + 
					"  `is_fetcher_link` int(11) NOT NULL COMMENT '是否提取链接',\r\n" + 
					"  `fetcher_link_time` datetime default NULL COMMENT '提取链接时间',\r\n" + 
					"  `site_id` int(11) NOT NULL COMMENT '来源网址编号',\r\n" + 
					"  `down_error_num` int(11) NOT NULL COMMENT '下载失败次数',\r\n" + 
					"  PRIMARY KEY  (`link_id`),\r\n" + 
					"  UNIQUE KEY `uq_md5_link` (`md5_link`),\r\n" + 
					"  KEY `idx_site_fetcher` (`site_id`,`is_fetcher_content`,`is_fetcher_link`)\r\n" + 
					") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='爬取的链接内容表';";
			batchExec(sql, baseDao);
			LOGGER.info("|===================================================");
		}
	}
	private static void createSpiderRule(BaseDao baseDao) {
		try {
			baseDao.queryForLong("select count(*) from spider_rule where 1=2");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[spider_rule]: " + e.getMessage());
			String sql = "CREATE TABLE `spider_rule` (\r\n" + 
					"  `rule_id` int(11) NOT NULL auto_increment COMMENT '编号',\r\n" + 
					"  `regex` varchar(200) NOT NULL COMMENT '页面表达式',\r\n" + 
					"  `title_select` varchar(100) NOT NULL COMMENT '标题选择器',\r\n" + 
					"  `content_select` varchar(100) NOT NULL COMMENT '内容选择器',\r\n" + 
					"  `site_id` int(11) NOT NULL COMMENT '网站编号',\r\n" + 
					"  `is_enable` int(11) NOT NULL COMMENT '是否启用[0否、1是]',\r\n" + 
					"  PRIMARY KEY  (`rule_id`),\r\n" + 
					"  UNIQUE KEY `uq_regex_site_id` (`regex`,`site_id`)\r\n" + 
					") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='爬取规则表';";
			batchExec(sql, baseDao);
			LOGGER.info("|===================================================");
		}
	}
	private static void createSpiderSite(BaseDao baseDao) {
		try {
			baseDao.queryForLong("select count(*) from spider_site where 1=2");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[spider_site]: " + e.getMessage());
			String sql = "CREATE TABLE `spider_site` (\r\n" + 
					"  `site_id` int(11) NOT NULL auto_increment COMMENT '编号',\r\n" + 
					"  `type` int(11) NOT NULL COMMENT '类型[10普通网站、20公众号]',\r\n" + 
					"  `name` varchar(50) NOT NULL COMMENT '名称',\r\n" + 
					"  `rule` varchar(255) NOT NULL COMMENT '爬取规则',\r\n" + 
					"  `rule_time` datetime NULL COMMENT '爬取规则时间',\r\n" + 
					"  `url` varchar(255) NOT NULL COMMENT '爬取地址',\r\n" + 
					"  `domain` varchar(255) NOT NULL COMMENT '域名',\r\n" + 
					"  `is_enable` int(11) NOT NULL COMMENT '是否启用[0否、1是]',\r\n" + 
					"  PRIMARY KEY  (`site_id`),\r\n" + 
					"  UNIQUE KEY `uq_url` (`url`)\r\n" + 
					") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='爬取网站表';";
			batchExec(sql, baseDao);
			LOGGER.info("|===================================================");
		}
	}
	private static void createSysConfig(BaseDao baseDao) {
		try {
			baseDao.queryForLong("select count(*) from sys_config where 1=2");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[sys_config]: " + e.getMessage());
			String sql = "CREATE TABLE `sys_config` (\r\n" + 
					"  `id` int(11) NOT NULL auto_increment COMMENT '编号',\r\n" + 
					"  `code` varchar(50) NOT NULL COMMENT '编码',\r\n" + 
					"  `name` varchar(50) NOT NULL COMMENT '名称',\r\n" + 
					"  `value` varchar(100) NOT NULL COMMENT '值',\r\n" + 
					"  `remark` varchar(100) default NULL COMMENT '描叙',\r\n" + 
					"  `exp1` varchar(100) default NULL COMMENT '扩展1',\r\n" + 
					"  `exp2` varchar(100) default NULL COMMENT '扩展2',\r\n" + 
					"  PRIMARY KEY  (`id`)\r\n" + 
					") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='系统配置';";
			batchExec(sql, baseDao);
			sql = "INSERT INTO `sys_config`(`id`,`code`,`name`,`value`,`remark`,`exp1`,`exp2`)\r\n" + 
					" VALUES (1,'sleep.spider.link','爬取地址下载页面间隔时间(单位:ms)','1000',default,default,default);\r\n" + 
					"INSERT INTO `sys_config`(`id`,`code`,`name`,`value`,`remark`,`exp1`,`exp2`)\r\n" + 
					" VALUES (2,'sleep.analysis.link','分析链接间隔时间(单位:ms)','300',default,default,default);\r\n" + 
					"INSERT INTO `sys_config`(`id`,`code`,`name`,`value`,`remark`,`exp1`,`exp2`)\r\n" + 
					" VALUES (3,'sleep.fetcher.page','处理符合规则的页面并下载(单位:ms)','300',default,default,default);\r\n" + 
					"INSERT INTO `sys_config`(`id`,`code`,`name`,`value`,`remark`,`exp1`,`exp2`)\r\n" + 
					" VALUES (4,'down.link.error.max.num','下载链接失败达到指定次，则移除下载','1',default,default,default);\r\n" + 
					"INSERT INTO `sys_config`(`id`,`code`,`name`,`value`,`remark`,`exp1`,`exp2`)\r\n" + 
					" VALUES (5,'link.repeat.extract.hour','对链接重复提取间隔(单位:小时)','12',default,default,default);\r\n" + 
					"\r\n" + 
					"INSERT INTO `sys_config`(`id`,`code`,`name`,`value`,`remark`,`exp1`,`exp2`)\r\n" + 
					" VALUES (6,'joblog.save.day','调度记录保存天数','7',default,default,default);\r\n" + 
					"INSERT INTO `sys_config`(`id`,`code`,`name`,`value`,`remark`,`exp1`,`exp2`)\r\n" + 
					" VALUES (7,'clean.cron','清空调度记录表达式','0 0 23 * * ?',default,default,default);\r\n" + 
					"INSERT INTO `sys_config`(`id`,`code`,`name`,`value`,`remark`,`exp1`,`exp2`)\r\n" + 
					" VALUES (8,'serv.save.day','已停止的服务保存天数','15',default,default,default);\r\n" + 
					"INSERT INTO `sys_config`(`id`,`code`,`name`,`value`,`remark`,`exp1`,`exp2`)\r\n" + 
					" VALUES (9,'lock.destroy.time','消耗服务和任务的时间[单位:s]','15',default,default,default);\r\n" + 
					"INSERT INTO `sys_config`(`id`,`code`,`name`,`value`,`remark`,`exp1`,`exp2`)\r\n" + 
					" VALUES (10,'leader.cron','Leader的时间表达式','0/5 * * * * ?',default,default,default);";
			batchExec(sql, baseDao);
			LOGGER.info("|===================================================");
		}
	}
	private static void createSysUser(BaseDao baseDao) {
		try {
			baseDao.queryForLong("select count(*) from sys_user where 1=2");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[sys_user]: " + e.getMessage());
			String sql = "CREATE TABLE `sys_user` (\r\n" + 
					"  `id` int(11) NOT NULL auto_increment COMMENT '编号',\r\n" + 
					"  `username` varchar(30) NOT NULL COMMENT '用户名',\r\n" + 
					"  `password` varchar(80) NOT NULL COMMENT '密码',\r\n" + 
					"  `nickname` varchar(30) NOT NULL COMMENT '昵称',\r\n" + 
					"  `addtime` datetime NOT NULL COMMENT '添加时间',\r\n" + 
					"  `adduser` int(11) NOT NULL COMMENT '添加人',\r\n" + 
					"  `status` int(11) NOT NULL COMMENT '状态【0正常、1冻结】',\r\n" + 
					"  PRIMARY KEY  (`id`),\r\n" + 
					"  UNIQUE KEY `unique_username` (`username`)\r\n" + 
					") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户';";
			batchExec(sql, baseDao);
			sql = "INSERT INTO `sys_user`(`id`,`username`,`password`,`nickname`,`addtime`,`adduser`,`status`)\r\n" + 
					" VALUES (1,'admin','" + SysUserUtil.encodePassword("123456") + "','管理员',now(),1,0)";
			baseDao.saveSql(sql);
			LOGGER.info("|===================================================");
		}
	}

	private static void batchExec(String sql, BaseDao baseDao) {
		List<String> sqls = FrameStringUtil.toArray(sql, ";");
		for (String s : sqls) {
			if (FrameStringUtil.isEmpty(s)) {
				continue;
			}
			baseDao.updateSql(s);
		}
	}
}
